﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{

    public class ClassDal
    {
        public DapperHelper dapperHelper { get; set; }

        public ILog log { get; set; }

        /// <summary>
        /// 通过视图获取所有班级信息
        /// </summary>
        /// <returns></returns>
        public List<ClassView> QueryAllClass()
        {
            List<ClassView> classes = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_CLASS_INFO";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                classes = a.Query<ClassView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return classes;
        }

        /// <summary>
        /// 根据班级ID查询视图信息
        /// </summary>
        /// <param name="classId"></param>
        /// <returns></returns>
        public ClassView QueryClassId(string classId)
        {
            ClassView classView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_CLASS_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where(" s.new_classId=@new_classId and s.statecode = 0 ");
                sqlParams.Add("@new_classId", classId);

                classView = a.QueryFirstOrDefault<ClassView>(sqlTemplate.RawSql, sqlParams);
            });

            return classView;
        }

        /// <summary>
        /// 根据班级名称查询视图信息
        /// </summary>
        /// <param name="communityId"></param>
        /// <returns></returns>
        public List<ClassView> QueryClassByName(string classname)
        {
            List<ClassView> classView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_CLASS_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where(" CHARINDEX('@new_name',s.new_name)>0 and s.statecode = 0 ");
                sqlParams.Add("@new_name", classname);

                classView = a.Query<ClassView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return classView;
        }

        /// <summary>
        /// 根据省市区县名称，班级名称(可空)查询班级视图信息
        /// </summary>
        /// <param name="communityId"></param>
        /// <returns></returns>
        public List<ClassView> QueryClassByProviceCityCounty(string provice,string city,string county,string classname ,string hospital,int stage = 0, Guid? schoolid = null)
        {
            List<ClassView> classView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_CLASS_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(classname))
                {
                    sqlBuilder.Where(" charindex(@new_name,s.new_name)>0 ");
                    sqlParams.Add("@new_name", classname);
                }
                if (stage != 0)
                {
                    sqlBuilder.Where("s.new_stage=@new_stage");
                    sqlParams.Add("@new_stage", stage);
                }
                if (schoolid != null)
                {
                    sqlBuilder.Where("s.new_school=@new_school");
                    sqlParams.Add("@new_school", schoolid);
                }
                if (!string.IsNullOrWhiteSpace(hospital))
                {
                    sqlBuilder.Where("s.new_hospital=@new_hospital");
                    sqlParams.Add("@new_hospital", hospital);
                }
                sqlBuilder.Where("s.new_provincename=@new_provincename and  s.statecode=0 ");
                sqlParams.Add("@new_provincename", provice);
                if (!string.IsNullOrWhiteSpace(city))
                {
                    sqlBuilder.Where(" s.new_cityname=@new_cityname");
                    sqlParams.Add("@new_cityname", city);
                }

                if (!string.IsNullOrWhiteSpace(county))
                {
                    sqlBuilder.Where(" s.new_areaname=@new_areaname");
                    sqlParams.Add("@new_areaname", county);
                }

                classView = a.Query<ClassView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return classView;
        }
        /// <summary>
        /// 根据省市区县名称，学段(可空)，学校id查询班级视图信息
        /// </summary>
        /// <param name="provice">省</param>
        /// <param name="city">市</param>
        /// <param name="county">区</param>
        /// <param name="hospital">医院ID</param>
        /// <param name="stage">学段</param>
        /// <param name="schoolid">学校ID</param>
        /// <returns></returns>
        public List<ClassView> QueryClassByPCAandStage(string provice, string city, string county,string hospital, int stage=0,Guid?schoolid=null)
        {
            List<ClassView> classView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_CLASS_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (stage!=0)
                {
                    sqlBuilder.Where("s.new_stage=@new_stage");
                    sqlParams.Add("@new_stage", stage);
                }
                if (schoolid != null)
                {
                    sqlBuilder.Where("s.new_school=@new_school");
                    sqlParams.Add("@new_school", schoolid);
                }
                if (!string.IsNullOrWhiteSpace(hospital))
                {
                    sqlBuilder.Where("s.new_hospital=@new_hospital");
                    sqlParams.Add("@new_hospital", hospital);
                }
                sqlBuilder.Where(" s.new_provincename=@new_provincename and s.statecode=0 ");
                sqlParams.Add("@new_provincename", provice);
                if(!string.IsNullOrWhiteSpace(city))
                {
                    sqlBuilder.Where(" s.new_cityname=@new_cityname");
                    sqlParams.Add("@new_cityname", city);
                }

                if (!string.IsNullOrWhiteSpace(county))
                {
                    sqlBuilder.Where(" s.new_areaname=@new_areaname");
                    sqlParams.Add("@new_areaname", county);
                }

                classView = a.Query<ClassView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return classView;
        }
        /// <summary>
        /// 根据班级信息和医院ID获取班级列表
        /// </summary>
        /// <param name="classinfo"></param>
        /// <param name="hospitalId"></param>
        /// <returns></returns>
        public List<ClassView> getClassListByConditions(ClassView classinfo, Guid hospitalId)
        {
            List<ClassView> classView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_CLASS_INFO as s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (classinfo.new_province != Guid.Empty)
                {
                    sqlBuilder.Where("s.new_province=@new_province");
                    sqlParams.Add("@new_province", classinfo.new_province);
                }
                if (classinfo.new_city != Guid.Empty)
                {
                    sqlBuilder.Where("s.new_city=@new_city");
                    sqlParams.Add("@new_city", classinfo.new_city);
                }
                if (classinfo.new_area != Guid.Empty)
                {
                    sqlBuilder.Where("s.new_area=@new_area");
                    sqlParams.Add("@new_area", classinfo.new_area);
                }
                if (classinfo.new_stage != 0)
                {
                    sqlBuilder.Where("s.new_stage=@new_stage");
                    sqlParams.Add("@new_stage", classinfo.new_stage);
                }
                if (classinfo.new_classId !=Guid.Empty)
                {
                    sqlBuilder.Where("s.new_classId=@new_classId");
                    sqlParams.Add("@new_classId", classinfo.new_classId);
                }
                if (!string.IsNullOrWhiteSpace(classinfo.new_classnumber))
                {
                    sqlBuilder.Where("s.new_classnumber=@new_classnumber");
                    sqlParams.Add("@new_classnumber", classinfo.new_classnumber);
                }
                if (classinfo.new_hospital != Guid.Empty)
                {
                    sqlBuilder.Where("s.new_hospital=@new_hospital");
                    sqlParams.Add("@new_hospital", classinfo.new_hospital);
                }
                sqlBuilder.OrderBy("new_name collate Chinese_PRC_CS_AS_KS_WS");
                log.Info(string.Format("ClassDal对像中,getClassListByConditions方法 ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                classView = a.Query<ClassView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return classView;
        }


        /// <summary>
        /// 根据学校ID或班级名称查询视图信息
        /// 原程序 ：creeningServiceImpl-> queryClass(String schoolId, String className)
        /// </summary>
        /// <param name="schoolId">学校ID</param>
        /// <param name="classname">班级名称</param>
        /// <returns></returns>
        public IEnumerable<ClassView> QueryClassBySchoolIdAndClassName(Guid schoolId, string classname)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ClassView>>(a =>
             {
                 string sql = @"select * from AIER_CLASS_INFO s /**where**/  ";
                 var sqlTemplate = sqlBuilder.AddTemplate(sql);

                 if (schoolId != null)
                 {
                     sqlBuilder.Where("s.new_school=@new_school");
                     sqlParams.Add("@new_school", schoolId);
                 }

                 if (!string.IsNullOrEmpty(classname))
                 {
                     sqlBuilder.Where("s.new_name=@new_name");
                     sqlParams.Add("@new_name", classname);
                 }

                 return a.Query<ClassView>(sqlTemplate.RawSql,sqlParams);
             });
        }
    }
}
